"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 二级分区
Case Name   : range_list二级分区表:collation/字段排序/expression/重建索引分区
Create At   : 2022/2/17
@zou_jialiang0501090252
Description :
    1.创建表空间
    2.创建二级分区表,指定collate "C",并插入数据
    3.类型为int的列创建索引指定collate
    4.查询表数据和col_1列指定collate的数据
    5.查询数字1指定collate "C"和col_2指定collate "POSIX"的数据
    6.修改字段数据类型text和排序规则"POSIX"
    7.定义不同列和表的注释
    8.修改字段数据类型
    9.创建local索引,指定不同的字段排序方法并查看执行计划
    10.设置索引不可用,查看执行计划
    11.rebuild重建索引,查看执行计划
    12.reindex重建索引,查看执行计划
    13.创建local索引,指定upper并查看执行计划
    14.创建global索引
    15.创建local索引,指定substr并查看执行计划
    16.清理环境
Expect      :
    1.成功
    2.成功
    3.合理报错
    4.成功
    5.合理报错
    6.合理报错
    7.成功
    8.合理报错
    9.成功
    10.成功,无Index Cond
    11.成功,有Index Cond
    12.成功,有Index Cond
    13.成功
    14.合理报错
    15.成功
    16.清理成功
History     :
"""
import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class Subpartition(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.primaryNode = Node('PrimaryDbUser')
        self.constant = Constant()
        self.t_name = 't_subpartition_0252'
        self.ts_name = 'ts_suibpartition_0252'
        self.i_name = 'i_subpartition_0252'

    def test_subpartition(self):
        text = '-----step1:创建表空间 expect:成功-----'
        self.log.info(text)
        create_sql = f"drop table if exists {self.t_name};" \
            f"drop tablespace if exists {self.ts_name};" \
            f"create tablespace {self.ts_name} relative location " \
            f"'{self.ts_name}';"
        msg = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(msg)
        self.assertIn(self.constant.TABLESPCE_CREATE_SUCCESS, msg,
                      '执行失败:' + text)

        text = '-----step2:创建二级分区表,指定collate "C",并插入数据 expect:成功-----'
        self.log.info(text)
        create_sql = f'create table {self.t_name}(col_1 text collate "C" ,' \
            f'col_2 int ,col_3 varchar2 ( 30 ) ,col_4 int ) ' \
            f'tablespace {self.ts_name} ' \
            f'partition by range (col_1) subpartition by list (col_2) ' \
            f'( partition p_range_1 values less than(-10) ' \
            f'  ( subpartition p_list_1_1 values(-1,-2,-3,-4,-5),' \
            f'    subpartition p_list_1_2 values(default)), ' \
            f'  partition p_range_2 values less than(20) ' \
            f'  ( subpartition p_list_2_1 values(6,7,8,9,10), ' \
            f'    subpartition "opengauss" values(default)), ' \
            f'  partition p_range_3 values less than(30) ' \
            f'  ( subpartition p_list_3_1 values(default)), ' \
            f'  partition p_range_4 values less than(40) ' \
            f'  ( subpartition p_list_4_1 values(default)), ' \
            f'  partition p_range_5 values less than(maxvalue) ' \
            f') enable row movement; ' \
            f'insert into {self.t_name} values (generate_series(0, 19),' \
            f'generate_series(0, 1000),generate_series(0, 99));' \
            f'insert into {self.t_name} values(1,11,1,1),(4,41,4,4),' \
            f'(5,54,5,5),(8,87,8,8),(9,19,9,9);'
        cmd = f"source {macro.DB_ENV_PATH};" \
            f"gsql -d {self.primaryNode.db_name} " \
            f"-p {self.primaryNode.db_port} -c '{create_sql}';"
        self.log.info(cmd)
        msg = self.primaryNode.sh(cmd).result()
        self.log.info(msg)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, msg, '执行失败:' + text)
        self.assertEqual(2, msg.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败:' + text)

        text = '-----step3:类型为int的列创建索引指定collate expect:合理报错-----'
        self.log.info(text)
        sql = f'create unique index on {self.t_name}(col_2 collate "C" );'
        cmd = f"source {macro.DB_ENV_PATH};" \
            f"gsql -d {self.primaryNode.db_name} " \
            f"-p {self.primaryNode.db_port} -c '{sql}';"
        self.log.info(cmd)
        msg = self.primaryNode.sh(cmd).result()
        self.log.info(msg)
        self.assertIn('collations are not supported by type integer', msg,
                      '执行失败:' + text)

        text = '-----step4:查询表数据和col_1列指定collate的数据 expect:成功-----'
        self.log.info(text)
        sql = f'select * from {self.t_name} limit 10;' \
            f'select * from {self.t_name} order by col_1 collate "C" ' \
            f'limit 10;'
        cmd = f"source {macro.DB_ENV_PATH};" \
            f"gsql -d {self.primaryNode.db_name} " \
            f"-p {self.primaryNode.db_port} -c '{sql}';"
        self.log.info(cmd)
        msg = self.primaryNode.sh(cmd).result()
        self.log.info(msg)
        self.assertEqual(2, msg.count('10 rows'), '执行失败:' + text)

        text = '-----step5:查询数字1指定collate "C"和col_2指定collate "POSIX"的数据 ' \
               'expect:合理报错-----'
        self.log.info(text)
        sql = f'select * from {self.t_name} order by 1 ' \
            f'collate "C" limit 10;' \
            f'select * from {self.t_name} order by col_2 ' \
            f'collate "POSIX" limit 10;'
        cmd = f"source {macro.DB_ENV_PATH};" \
            f"gsql -d {self.primaryNode.db_name} " \
            f"-p {self.primaryNode.db_port} -c '{sql}';"
        self.log.info(cmd)
        msg = self.primaryNode.sh(cmd).result()
        self.log.info(msg)
        self.assertIn('collations are not supported by type integer', msg,
                      '执行失败:' + text)

        text = '-----step6:修改字段数据类型text和排序规则"POSIX" expect:合理报错-----'
        self.log.info(text)
        sql = f'alter table {self.t_name} alter col_2 type text ' \
            f'collate "POSIX";' \
            f'alter table {self.t_name} alter col_1 type text ' \
            f'collate "POSIX";' \
            f'alter table {self.t_name} alter col_4 type text ' \
            f'collate "POSIX";'
        cmd = f"source {macro.DB_ENV_PATH};" \
            f"gsql -d {self.primaryNode.db_name} " \
            f"-p {self.primaryNode.db_port} -c '{sql}';"
        self.log.info(cmd)
        msg = self.primaryNode.sh(cmd).result()
        self.log.info(msg)
        self.assertIn(self.constant.ALTER_TABLE_MSG, msg, '执行失败:' + text)
        self.assertEqual(2, msg.count('cannot alter data type'),
                         '执行失败:' + text)

        text = '-----step7:定义不同列和表的注释 expect:成功-----'
        self.log.info(text)
        sql = f"comment on column {self.t_name}.col_1 is " \
            f"'这是二级分区deyijifenqujian_key';" \
            f"comment on column {self.t_name}.col_2 is " \
            f"'这是二级分区deerjifenqujian_key';" \
            f"comment on column {self.t_name}.col_3 is '这是二级分区de普通字段';" \
            f"comment on column {self.t_name}.col_4 is '这是二级分区de普通字段';" \
            f"comment on table {self.t_name} is '这是二级分区表';"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertEqual(5, msg.count('COMMENT'), '执行失败:' + text)

        text = '-----step8:修改字段数据类型 expect:合理报错-----'
        self.log.info(text)
        sql = f"alter table {self.t_name} alter col_1 type text ;" \
            f"alter table {self.t_name} alter col_2 type text ;" \
            f"alter table {self.t_name} alter col_2 type int ;"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertEqual(2, msg.count(
            'cannot alter data type of subpartitioning'), '执行失败:' + text)

        text = '-----step9:创建local索引,指定不同的字段排序方法并查看执行计划 expect:成功-----'
        self.log.info(text)
        sql = f"drop index if exists {self.i_name};" \
            f"create index {self.i_name} on {self.t_name}(col_2 asc) local;" \
            f"drop index if exists  {self.i_name};" \
            f"create index {self.i_name} on {self.t_name}(col_2 desc) " \
            f"local;" \
            f"drop index if exists  {self.i_name};" \
            f"create index {self.i_name} on {self.t_name}(col_2 nulls " \
            f"first) local;" \
            f"drop index if exists  {self.i_name};" \
            f"create index {self.i_name} on {self.t_name}(col_2 nulls " \
            f"last ) local;" \
            f"explain analyze select * from {self.t_name} where col_2 in " \
            f"(select col_1 from {self.t_name}  where col_1 >10 and " \
            f"col_1<100) order by 2 asc limit 100;"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertEqual(4, msg.count(self.constant.CREATE_INDEX_SUCCESS),
                         '执行失败:' + text)
        self.assertIn('Nested Loop', msg, '执行失败:' + text)

        text = '-----step10:设置索引不可用,查看执行计划 expect:成功,无Index Cond-----'
        self.log.info(text)
        sql = f"alter index {self.i_name} unusable;" \
            f"explain analyze select * from {self.t_name} where col_2 in " \
            f"(select col_1 from {self.t_name}  where col_1 >10 and " \
            f"col_1<100) order by 2 asc limit 100;"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn(self.constant.ALTER_INDEX_SUCCESS_MSG, msg,
                      '执行失败:' + text)
        self.assertNotIn('Index Cond', msg, '执行失败:' + text)

        text = '-----step11:rebuild重建索引,查看执行计划 expect:成功,有Index Cond-----'
        self.log.info(text)
        sql = f"alter index {self.i_name} rebuild;" \
            f"explain analyze select * from {self.t_name} where col_2 in " \
            f"(select col_1 from {self.t_name}  where col_1 >10 and " \
            f"col_1<100) order by 2 asc limit 100;"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn(self.constant.REINDEX_SUCCESS_MSG, msg, '执行失败:' + text)
        self.assertIn('Index Cond', msg, '执行失败:' + text)

        text = '-----step12:reindex重建索引,查看执行计划 expect:成功,有Index Cond-----'
        self.log.info(text)
        sql = f"reindex index {self.i_name};" \
            f"explain analyze select * from {self.t_name} where col_2 in " \
            f"(select col_1 from {self.t_name}  where col_1 >10 and " \
            f"col_1<100) order by 2 asc limit 100;"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn(self.constant.REINDEX_SUCCESS_MSG, msg, '执行失败:' + text)
        self.assertIn('Index Cond', msg, '执行失败:' + text)

        text = '-----step13:创建local索引,指定upper并查看执行计划 expect:成功-----'
        self.log.info(text)
        sql = f"create index on {self.t_name}(upper(col_2)) local;" \
            f"explain  analyze select * from {self.t_name} where " \
            f"upper(col_2) in  (select col_1 from {self.t_name}  " \
            f"where col_1 >10 and col_1<100) order by 2 asc limit 100;"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS, msg, '执行失败:' + text)
        self.assertIn('upper((col_2)::text)', msg, '执行失败:' + text)

        text = '-----step14:创建global索引 expect:合理报错-----'
        self.log.info(text)
        sql = f"create index on {self.t_name}(substr(col_2,1 ,4)) ;"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn(
            'Global partition index does not support EXPRESSION index', msg,
            '执行失败:' + text)

        text = '-----step15:创建local索引,指定substr并查看执行计划 expect:成功-----'
        self.log.info(text)
        sql = f"create index on {self.t_name}(substr(col_2,1 ,4)) ;" \
            f"create index on {self.t_name}(substr(col_2,1 ,4)) local;" \
            f"explain analyze select substr(col_2,1 ,4) from {self.t_name} " \
            f"where substr(col_2,1 ,4) in (select col_1 from {self.t_name} " \
            f"where col_1 >10 and col_1<100 ) and substr(col_2,1 ,4) > 1 " \
            f"order by 1 limit 100;"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS, msg, '执行失败:' + text)
        self.assertIn('substr((col_2)::text, 1, 4)', msg, '执行失败:' + text)

    def tearDown(self):
        self.log.info('-----step16:清理环境-----')
        text_1 = '-----删除表 expect:成功-----'
        self.log.info(text_1)
        del_cmd = f'drop table if exists {self.t_name};' \
            f'drop tablespace if exists {self.ts_name};'
        msg = self.pri_sh.execut_db_sql(del_cmd)
        self.log.info(msg)

        self.assertIn(self.constant.TABLESPCE_DROP_SUCCESS, msg,
                      '执行失败:' + text_1)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, msg, '执行失败:' + text_1)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
